--1.准备三张来源表，把建表封装在shell脚本中
#!/bin/bash
####数仓小项目
####创建日期：2024-4-8
####创建人员：2401全体大神
####开始编写脚本
db_name1=ods2401
db_name2=dw2401
db_name3=dm2401
t1=ods_cust_info
t2=dw_addr_info
t3=dw_product_info
t4=dw_cust_info
t5=xs_rank
echo "*******创建客户基本信息表************"
create_table="
create table if not exists ${db_name1}.${t1}(
    cust_id string,
    cust_name string,
    product_code int,
    txt_amt int,
    addrno int,
    age int,
    dt string
)
row format delimited fields terminated by '\t';"
##使用hive命令调用建表语句
hive -e "$create_table"
echo $?
echo "*****客户基表已创建完成**********"
echo "*******创建地址码表************"
create_table2="
create table if not exists ${db_name2}.${t2}(
    addrno int,
    addrname string,
    addr_code string
)row format delimited fields terminated by ',';"

##使用hive命令调用建表语句
echo "$create_table2"|hive
echo $?
echo "*****地址码表已创建完成**********"
echo "*******创建产品信息表************"
create_table3="
create table if not exists ${db_name2}.${t3}(
    product_code int,
    product_name string,
    product_type string
)row format delimited fields terminated by '\t';"
##使用hive命令调用建表语句
hive -e "$create_table3"
echo $?
echo "*****产品信息表已创建完成**********"
##在ods层清洗完成后的客户基表抽取到dw层
create_table4="
create table if not exists ${db_name2}.${t4}(
    cust_id string,
    cust_name string,
    product_code int,
    txt_amt int,
    addrno int,
    age int,
    dt string,
    etl_dt string
)
row format delimited fields terminated by '\t';"
##使用hive命令调用建表语句
hive -e "$create_table4"
echo $?
echo "****dw层客户基表已创建完成**********"
echo "*******创建dm层的汇总表************"
create_table5="
create table if not exists ${db_name3}.${t5}(
    addr_name string,
    product_name string,
    s_amt int,
    rn int,
    etl_dt string
)row format delimited fields terminated by '\t';"
##使用hive命令调用建表语句
hive -e "$create_table5"
echo $?
echo "****dm层汇总表已创建完成**********"

2.给三张来源表准备源数据
准备ods_cust_info 源数据
100AAA1	李三SSS	1	100	1	20	2023-1-1
100SS2	张三t88	2	1000	2	21	2023-2-2
100j3j	马三99	3	50000	3	45	2023-2-3
1004kj	范三99	4	12000	4	35	2023-2-7
1005kj	王三99	5	80000	5	25	2023-3-3
100jk7	范雨99	1	20000	1	35	2023-2-3
1008k	刘六99	2	30000	1	25	2023-2-3
100j8j	刘五99	3	40000	1	25	2023-2-3
j100j9j	刘八99	4	50000	2	35	2023-2-3
1010li	刘久99	5	60000	4	26	2023-2-3
1011kj	刘熊99	1	70000	4	27	2023-2-3
1012jk	刘马99	2	80000	2	28	2023-2-3
1013雨j	张其99	3	90000	2	51	2023-2-3
1014op	刘琪99	4	110000	2	52	2023-2-3
1015kj	王丹丹99	5	10000	3	45	2023-2-3
1016kj	刘丹丹k9	5	10000	3	45	2023-2-3
1017jy	马丹1k	1	20000	5	55	2023-2-3
1018jkj	欧阳文l9	1	10000	3	45	2023-2-3
1019jkj	何其99	2	60000	5	65	2023-2-3
1020jkj	朱天99	3	70000	5	65	2023-2-3
2001kj	朱丽99	3	650000	5	45	2023-2-3
2002jkj	李璐99	2	1000000	4	35	2023-2-3
2003jkj	李强99	4	780000	4	38	2023-2-3
2004jkj	李丁99	1	80000	4	37	2023-2-3
2005jkj	李勇99	2	10000	3	45	2023-2-3
2008jkj	李雪99	4	10000	3	45	2023-2-3

--导入到hive的ods的客户基表中
load data local inpath '/2401/o1.txt' into table ods2401.ods_cust_info;

--3.编写脚本，进行数据的清洗
封装在存过或者shell脚本中的sql语句，必须现在数据库中跑通了让后在封装到我们的存过或者脚本中
--使用正则表达式进行数据的清洗
select regexp_replace(cust_id,'[^0-9]+','') cust_id,
regexp_replace(cust_name,'[^一-龥]+','') cust_name,current_timestamp from ods2401.ods_cust_info; 
--开始编写清洗脚本
#!/bin/sh
###清洗ods层客户基表，并把数据通过hql抽取到dw层
##编写日期：2024-4-8
##编写人员：2401全体大神
##来源表：ods_cust_info   落地表:dw_cust_info
echo "--------------开始清洗客户基表，把数据从ods抽取到dw-----------"
hive -v -e"set hive.exec.mode.local.auto=true; \
insert overwrite table dw2401.dw_cust_info
select 
regexp_replace(cust_id,'[^0-9]+','') cust_id,
regexp_replace(cust_name,'[^一-龥]+','') cust_name,
product_code,
txt_amt,
addrno,
age,
dt,
current_timestamp etl_dt from ods2401.ods_cust_info;"
echo "----------数据清洗完成，请进行校验--------------"
echo "------------开始校验--------------------"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from ods2401.ods_cust_info;"`
echo "-----ods层来源表数据量为:${c1}条*************"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(1) from dw2401.dw_cust_info;"`
echo "-----dw层落地表数据量为:${c2}条*************"
if [ "${c1}" == "${c2}" ];then
    echo "----数据量一致，清洗成功"
else
    echo "清洗失败，请检查脚本"
fi

4.
--接下来给区域表和产品表准备数据
--区域表详细信息：
1,华南,aaa
2,华北,bbb
3,华中,ccc
4,西南,ddd
5,西部,eee
5,西部,eee
5,西部,eee
4,西南,ddd
--导数
load data local inpath '/2401/l2.txt' into table dw2401.dw_addr_info;
--产品表信息：
1       手机    电子产品
2       平板    电子产品
3       牙刷    日常用品
4       瓷碗    生活用品
5       鸡肉    食品产品
5       鸡肉    食品产品
5       鸡肉    食品产品
4       瓷碗    生活用品


--把数据导入到dw层表格中
load data local inpath'/2401/l3.txt' into table dw2401.dw_product_info;

5.指标计算：
查询每个区域不同产品的销售，以及排名情况
落地表的字段：
区域名称 产品名称 总额 排名 抽取时间
区域名称：根据基表关联区域信息表，addrno
产品名称：根据基表关联产品信息表，product_code
总额：聚合计算
排名：窗口函数
抽取时间：直取
客户基表是主表，区域和产品是从表，关联之前需要对从表的数据进行去重 group by 全字段
采用left join

6.编写计算的hql
select a.*,
        row_number() over(partition by addr_name order by s_amt) rn,
        current_timestamp etl_dt
        from
(select b.addrname addr_name,
       c.product_name,
       sum(a.txt_amt) s_amt
 from 
dw2401.dw_cust_info a left join
(select 
    addrno,
    addrname,
    addr_code 
    from dw2401.dw_addr_info 
    group 
    by addrno,
    addrname,
    addr_code) b
on a.addrno=b.addrno left join 
(select 
    product_code,
    product_name,
    product_type 
    from dw2401.dw_product_info 
    group by 
    product_code,
    product_name,
    product_type) c
on a.product_code=c.product_code
    group by 
    b.addrname,
    c.product_name
    order by 
    b.addrname,
    c.product_name) a;

--编写shell脚本
#!/bin/bash
##把dw层的数据进行进行，抽取到dm层
##编写日期：2024-4-8
##编写人员：2401全体大神
##来源表 dw: dw_cust_info dw_product_info dw_addr_info 落地表：dm:xs_rank
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table dm2401.xs_rank
select a.*,
        row_number() over(partition by addrname order by s_amt) rn,
        current_timestamp etl_dt
        from
(select b.addrname addr_name,
       c.product_name,
       sum(a.txt_amt) s_amt
 from 
dw2401.dw_cust_info a left join
(select 
    addrno,
    addrname,
    addr_code 
    from dw2401.dw_addr_info 
    group 
    by addrno,
    addrname,
    addr_code) b
on a.addrno=b.addrno left join 
(select 
    product_code,
    product_name,
    product_type 
    from dw2401.dw_product_info 
    group by 
    product_code,
    product_name,
    product_type) c
on a.product_code=c.product_code
    group by 
    b.addrname,
    c.product_name
    order by 
    b.addrname,
    c.product_name) a;
"
echo "------抽取完成-------------"
